#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# @Time    : 2021/7/20 11:33
# @Author  : Samge
import json
import sys

from PyQt5 import QtWidgets
from PyQt5.QtCore import QRegExp
from PyQt5.QtGui import QIntValidator, QRegExpValidator
from PyQt5.QtWidgets import QWidget

from itkz.resources.custom_view.FieldListWidget import FieldListWidget
from itkz.resources.utils import config_util, sql_util
from itkz.resources.utils.log import Log
from itkz.resources.view_filter_input import Ui_Form


class FilterInputWidget(QWidget, Ui_Form):

    # 筛选字段key名
    fields_key = {}
    # 配置名
    settingsName = ''
    # 是否mysql
    IS_MYSQL = False
    # 其他查询参数列表
    other_params = []

    def __init__(self, parent=None, *args, **kwargs):
        super().__init__(parent, *args, **kwargs)
        self.setupUi(self)
        self.field_list = FieldListWidget()
        self.field_list_for_label = FieldListWidget()
        self.setupData()

    def set_table_fields(self, fields):
        """
        设置表的字段
        :param fields:
        :return:
        """
        self.field_list.setupFieldsKey(fields)
        self.field_list_for_label.setupFieldsKey(fields)

    def setupData(self):
        """
        数据初始化
        :return:
        """
        # 整数校验器[1,99]
        v_int = QIntValidator()
        v_int.setRange(1, 1000)

        # 数字字母校验器
        reg = QRegExp('[a-zA-Z0-9]+$')
        v_int_char = QRegExpValidator(self)
        v_int_char.setRegExp(reg)

        # url网址校验器
        reg = QRegExp('[%a-zA-z]+://[^\s]*')
        v_url = QRegExpValidator(self)
        v_url.setRegExp(reg)

        # 设置校验器
        self.et_page_size.setValidator(v_int)
        self.et_status.setValidator(v_int)
        self.et_dataId.setValidator(v_int_char)
        self.et_title.setValidator(v_url)

        # 设置字段绑定监听
        self.onFieldsBindListener(self.parseFieldsBind)

    def setupQSettingsName(self, name):
        """
        初始化缓存配置的根名字  可以理解为 [配置域]
        :param name:
        :return:
        """
        self.settingsName = name

    def setupFieldsKey(self, fields_key):
        """
        初始化字段配置
        :param fields_key:
        :return:
        """
        self.fields_key = fields_key
        self.et_order_by.setText(self.fields_key.get('field_order_by') or '')
        self.init_config_value()

    def reset_config_value(self):
        """
        重置配置显示
        :return:
        """
        self.et_page_size.setText('')
        self.et_status.setText('')
        self.et_title.setText('')
        self.et_dataId.setText('')
        self.et_keyword.setText('')
        self.cb_sort_type.setCurrentIndex(0)
        self.et_group_by.setText('')
        self.cb_sum.setChecked(False)
        self.cb_other_params.clear()

    def init_config_value(self):
        """
        初始化配置显示
        :return:
        """
        self.et_page_size.setText(config_util.value(f"{self.settingsName}/page_size_value") or '')
        self.et_status.setText(config_util.value(f"{self.settingsName}/status_value") or '')
        self.et_title.setText(config_util.value(f"{self.settingsName}/url_value") or '')
        self.et_dataId.setText(config_util.value(f"{self.settingsName}/md5_value") or '')
        self.et_keyword.setText(config_util.value(f"{self.settingsName}/keyword_value") or '')
        self.cb_sort_type.setCurrentIndex(int(config_util.value(f"{self.settingsName}/sort_type_index_value") or 0))
        self.et_group_by.setText(config_util.value(f"{self.settingsName}/group_by_value") or '')
        self.cb_sum.setChecked(config_util.value(f"{self.settingsName}/check_sum_value")=='true' or False)

        self.other_params = config_util.value(f"{self.settingsName}/other_params_value") or []
        self.cb_other_params.clear()
        self.cb_other_params.addItems(self.other_params)

    def save_config_value(self):
        """
        保存当前配置信息
        :return:
        """
        config_util.setValue(f"{self.settingsName}/page_size_value", self.et_page_size.text() or '')
        config_util.setValue(f"{self.settingsName}/status_value", self.et_status.text() or '')
        config_util.setValue(f"{self.settingsName}/url_value", self.et_title.text() or '')
        config_util.setValue(f"{self.settingsName}/md5_value", self.et_dataId.text() or '')
        config_util.setValue(f"{self.settingsName}/keyword_value", self.et_keyword.text() or '')
        config_util.setValue(f"{self.settingsName}/sort_type_index_value", self.cb_sort_type.currentIndex() or 0)
        config_util.setValue(f"{self.settingsName}/group_by_value", self.et_group_by.text() or '')
        config_util.setValue(f"{self.settingsName}/check_sum_value", self.cb_sum.isChecked() or False)
        config_util.setValue(f"{self.settingsName}/other_params_value", self.other_params or [])

    def get_data(self):
        return {
            'page_size': self.et_page_size.text(),
            'status': self.et_status.text(),
            'title': self.et_title.text(),
            'dataId': self.et_dataId.text(),
            'keyword': self.et_keyword.text(),
            'order_by': self.et_order_by.text(),
            'sort_type': self.cb_sort_type.currentText(),
            'group_by': self.et_group_by.text(),
            'other_params': self.cb_other_params.text(),
            'check_sum': self.cb_sum.isChecked(),
            'fields': self.field_list.get_fields(),
        }

    def show_field_list(self):
        """
        显示选择要查询的字段窗口
        :return:
        """
        if not self.field_list.isActiveWindow():
            self.field_list.show()
            self.field_list.activateWindow()
            self.field_list.showNormal()

    def show_field_list_for_bind(self, v):
        """
        显示字段绑定的窗口
        :param v:
        :return:
        """
        if not self.field_list_for_label.isActiveWindow():
            self.field_list_for_label.parse_select(is_checked_all=False)
            self.field_list_for_label.setupQSettingsName(self.settingsName)
            self.field_list_for_label.set_curr_bind_label_text(v)
            self.field_list_for_label.show()
            self.field_list_for_label.activateWindow()
            self.field_list_for_label.showNormal()

    def bind_field(self, v):
        """
        弹窗选择要绑定的字段
        :param v:
        :return:
        """
        print(f'bind_field {v}')
        self.show_field_list_for_bind(v)

    def onFieldsBindListener(self, func):
        """
        设置字段绑定的监听
        :param func:
        :return:
        """
        self.field_list_for_label.connect_clicked_slot(func)

    def parseFieldsBind(self, v):
        """
        处理【字段绑定】的监听结果
        :param v:
        :return:
        """
        print(f"parseFieldsBind {v}")
        result = json.loads(v)
        field_key = result.get('field_key')
        fields = result.get('fields')
        if not field_key:
            return
        self.fields_key[field_key] = fields
        config_util.setValue(f"{self.settingsName}/{field_key}", fields)

        field_first = fields[0] if fields else ''
        if 'field_order_by' == field_key:
            self.et_order_by.setText(field_first)
        elif 'field_group_by' == field_key:
            self.et_group_by.setText(field_first)
        elif 'field_query_fields' == field_key:
            self.et_query_fields.setText(','.join(fields))
        elif 'field_other_params' == field_key:
            curr_text = self.cb_other_params.currentText() or ''
            for field in fields or []:
                if field not in curr_text:
                    curr_text = f"{curr_text} and {field}=''" if curr_text else f"{field}=''"
            self.cb_other_params.setCurrentText(curr_text)

    def update_other_params(self, curr_text):
        """
        更新其他查询参数列表
        :param curr_text:
        :return:
        """
        if curr_text not in self.other_params:
            self.other_params.insert(0, curr_text)
        else:
            self.other_params.remove(curr_text)
            self.other_params.insert(0, curr_text)
        self.cb_other_params.clear()
        self.cb_other_params.addItems(self.other_params)

    def parseFieldsQuery(self, v):
        """
        处理【要查询的字段】的监听结果
        :param v:
        :return:
        """
        print(f"parseFieldsQuery {v}")
        result = json.loads(v)
        fields = result.get('fields')
        if not result.get('fields'):
            return
        config_util.setValue(f"{self.name()}/fields_query", fields)

    # 默认 高级查询 的sql模板
    SQL_TEMPLATE = "select {top} {query_fields} from {table_name} with(nolock) {where} {order_by} {sort_type}"
    # 默认 高级查询-分组查询 的sql模板
    SQL_TEMPLATE_GROUP = "select {group_by_key},count({group_by_key}) as sum1 from {table_name} with(nolock) {where} group by {group_by_key} order by sum1 {sort_type}"
    # 默认 高级查询-查询总数 的sql目标
    SQL_TEMPLATE_SUM = "select count(1) as sum1 from {table_name} with(nolock) {where}"

    # 默认 高级查询 的sql模板
    MYSQL_TEMPLATE = "select {query_fields} from {table_name} {where} {order_by} {sort_type} {top}"

    @property
    def get_filter_sql(self):
        """
        获取当前的高级查询sql语句
        :return:
        """
        # 表名，子类中要配置
        table_name = self.fields_key.get('table_name')
        if not table_name:
            Log().getlog().info("请先在子类中配置 table_name")
            return None

        # 查询数量
        top = f"top {self.et_page_size.text() or 10}"

        # 查询字段
        field_query_fields = self.fields_key.get('field_query_fields')
        if type(field_query_fields) is list:
            query_fields = ','.join(field_query_fields) if field_query_fields else '*'
        else:
            query_fields = field_query_fields or '*'

        # 排序key
        field_order_by = self.fields_key.get('field_order_by')
        if type(field_order_by) is list:
            order_by = f"order by {field_order_by[0]}" if field_order_by else ''
        else:
            order_by = f"order by {field_order_by}" if field_order_by else ''

        # 分组key
        group_by_key = self.fields_key.get('field_group_by')
        if type(group_by_key) is list:
            group_by_key = group_by_key[0] if group_by_key else ''
        else:
            group_by_key = group_by_key or ''

        # 其他参数
        other_params = self.cb_other_params.currentText()
        self.update_other_params(other_params)

        # 排序方式
        sort_type = self.cb_sort_type.currentText()

        # 拼接查询条件语句
        where = sql_util.get_where(self.fields_key.get('field_status'), self.et_status.text(), where=None)
        where = sql_util.get_where(self.fields_key.get('field_dataId'), self.et_dataId.text(), where=where)
        where = sql_util.get_where(self.fields_key.get('field_title'), self.et_title.text(), where=where)
        where = sql_util.get_where(self.fields_key.get('field_keyword'), self.et_keyword.text(), where=where, op_type='or')
        if where and other_params:
            where = f"{where} and {other_params}"
        elif other_params:
            where = other_params
        where = f"where {where}" if where else ''

        if self.cb_sum.isChecked():  # 查询总数
            sql_str = self.SQL_TEMPLATE_SUM.format(table_name=table_name, where=where)
        elif group_by_key:  # 查询分组
            sql_str = self.SQL_TEMPLATE_GROUP.format(group_by_key=group_by_key,
                                                  table_name=table_name,
                                                  where=where,
                                                  sort_type=sort_type)
        else:  # 默认高级查询
            template = self.MYSQL_TEMPLATE if self.IS_MYSQL else self.SQL_TEMPLATE
            sql_str = template.format(top=top,
                                    query_fields=query_fields,
                                    table_name=table_name,
                                    where=where,
                                    order_by=order_by,
                                    sort_type=sort_type)
        if self.IS_MYSQL:
            sql_str = sql_str\
                .replace('len(', 'length(')\
                .replace('top ', 'limit ')\
                .replace('with(nolock)', '')
        return sql_str


def main():
    app = QtWidgets.QApplication(sys.argv)
    gui = FilterInputWidget()
    gui.show()
    sys.exit(app.exec_())


if __name__ == '__main__':
    main()
